﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Gola.VN.Scb.Web.Common;
using System.Data;
using Gola.VN.Scb.Web.Common.Entities;
using Gola.VN.Scb.Web.Dal.Utilities;

namespace Gola.VN.Scb.Web.Dal
{
    public class ProductDal
    {
        public static IEnumerable<ProductEntity> GetAllProducts()
        {
            DaoPackage package = new DaoPackage();
            package.Channel = ChannelType.Sql;
            package.Event = DaoEvent.GetProducts;
            MainDao.GetInstant().Execute(package);

            return package.Output as IEnumerable<ProductEntity>;
//            try
//            {
//                var conn = DalUtilities.GetConnection();
//                const string sql = @"SELECT   [PRODUCT].[ID]
//                                      ,[PRODUCT].[PROVIDERID]
//                                      ,[PRODUCT].[CATEGORYID]
//                                      ,[PRODUCT].[CRAWLLINKID]
//                                      ,[PRODUCT].[TITLE]
//                                      ,[PRODUCT].[DESCRIPTION]
//                                      ,[PRODUCT].[IMAGE]
//                                      ,[PRODUCT].[LINK]
//                                      ,[PRODUCT].[PRICE]
//                                      ,[PRODUCT].[OLDPRICE]
//                                      ,[PRODUCT].[CURRENCY]
//                                      ,[PRODUCT].[PROMOTION]
//                                      ,[PRODUCT].[CREATEDDATE]
//                                      ,[PRODUCT].[PRODUCTID]
//                                      ,[PROVIDER].[NAME] AS PROVIDERNAME
//                                      ,[PROVIDER].[ADDRESS] AS PROVIDERADDRESS
//                                  FROM [PRODUCT] 
//                                  INNER JOIN [PROVIDER] ON PROVIDER.ID = PRODUCT.PROVIDERID";
//                using (var command = new SqlCommand(sql, conn))
//                {
//                    using (SqlDataReader reader = command.ExecuteReader())
//                    {
//                        // return SqlDataReaderHelper.GetProductEntities(reader);//todo: must be update
//                        return null;
//                    }
//                }
//            }
//            catch (SqlException ex)
//            {
//                throw new Exception("Error while get all products.", ex);
//            }
        }

        public static IEnumerable<CategoryEntity> GetAllCategores()
        {
            DaoPackage package = new DaoPackage();
            package.Channel = ChannelType.Sql;
            package.Event = DaoEvent.GetCategoriesAsFlat;
            MainDao.GetInstant().Execute(package);

            return package.Output as IEnumerable<CategoryEntity>;
//            try
//            {
//                var conn = DalUtilities.GetConnection();
//                const string sql = @"SELECT [ID]
//                                          ,[NAME]
//                                          ,[NOTES]
//                                          ,[PARENTID]
//                                      FROM [CATEGORY]";
//                using (var command = new SqlCommand(sql, conn))
//                {
//                    using (SqlDataReader reader = command.ExecuteReader())
//                    {
//                        // return SqlDataReaderHelper.GetCategoryEntities(reader);//todo must be replaced
//                        return null;
//                    }
//                }
//            }
//            catch (SqlException ex)
//            {
//                throw new Exception("Error while get all categories.", ex);
//            }
        }

        public static IEnumerable<ProviderEntity> GetAllProvider()
        {
            DaoPackage package = new DaoPackage();
            package.Channel = ChannelType.Sql;
            package.Event = DaoEvent.GetProviders;
            MainDao.GetInstant().Execute(package);

            return package.Output as IEnumerable<ProviderEntity>;
//            try
//            {
//                var conn = DalUtilities.GetConnection();
//                const string sql = @"SELECT [ID]
//                                            ,[NAME]
//                                            ,[ADDRESS], DISPLAYNAME, LOGO, EMAIL, PHONENUMBER, EXPRESSIONID
//                                        FROM [PROVIDER]";
//                using (var command = new SqlCommand(sql, conn))
//                {
//                    using (SqlDataReader reader = command.ExecuteReader())
//                    {
//                        // return SqlDataReaderHelper.GetProviderEntities(reader);//todo: must be replaced
//                        return null;
//                    }
//                }
//            }
//            catch (SqlException ex)
//            {
//                throw new Exception("Error while get all provideres.", ex);
//            }
        }

        public static int GetSystemInfo()
        {
            DaoPackage package = new DaoPackage();
            package.Channel = ChannelType.Sql;
            package.Event = DaoEvent.GetSysInfo;
            MainDao.GetInstant().Execute(package);

            return (int)package.Output;
            //try
            //{
            //    const string sql = "SELECT TOP 1 DATASTATUS FROM SYSTEMINFO";
            //    int result = 0;
            //    var conn = DalUtilities.GetConnection();
            //    using (var sqlCmm = new SqlCommand(sql, conn))
            //    {
            //        using (SqlDataReader reader = sqlCmm.ExecuteReader())
            //        {
            //            while (reader.Read())
            //            {
            //                result = reader.GetInt16(0);
            //            }
            //        }
            //    }
            //    return result;
            //}
            //catch (SqlException ex)
            //{
            //    throw new Exception("Error while get all categories.", ex);
            //}
        }

        public static void UpdateSystemInfo()
        {
            DaoPackage package = new DaoPackage();
            package.Channel = ChannelType.Sql;
            package.Event = DaoEvent.UpdateSysInfo;
            MainDao.GetInstant().Execute(package);

            //try
            //{
            //    const string sql = "UPDATE SYSTEMINFO SET DATASTATUS = 3";
            //    var conn = DalUtilities.GetConnection();
            //    using (var sqlCmm = new SqlCommand(sql, conn))
            //    {
            //        sqlCmm.ExecuteNonQuery();

            //    }
            //}
            //catch (SqlException ex)
            //{
            //    throw new Exception("Error while get all categories.", ex);
            //}
        }

        public static IEnumerable<LocationEntity> GetAllLocations()
        {
            DaoPackage package = new DaoPackage();
            package.Channel = ChannelType.Sql;
            package.Event = DaoEvent.GetLocations;
            MainDao.GetInstant().Execute(package);

            return package.Output as IEnumerable<LocationEntity>;

//            try
//            {
//                var conn = DalUtilities.GetConnection();
//                const string sql = @"SELECT  [LATITUDE]
//                                            ,[LONGTITUDE]
//                                            ,[ADDRESS]
//                                            ,[TITLE]
//                                            ,[PROVIDERID]
//                                        FROM [DBO].[LOCATION]";
//                using (var command = new SqlCommand(sql, conn))
//                {
//                    using (SqlDataReader reader = command.ExecuteReader())
//                    {
//                        return DataTableHelper.GetLocationEntities(reader);
//                    }
//                }
//            }
//            catch (SqlException ex)
//            {
//                throw new Exception("Error while get all locations.", ex);
//            }            
        }

        public static List<SearchHistoryEntity> GetSearchHistories()
        {
            DaoPackage package = new DaoPackage();
            package.Channel = ChannelType.Sql;
            package.Event = DaoEvent.GetSearchHistories;
            MainDao.GetInstant().Execute(package);

            return package.Output as List<SearchHistoryEntity>;
//            try
//            {
//                var conn = DalUtilities.GetConnection();
//            string sql = @"SELECT   
//                                      Text,
//                                      Link,
//                                      Count,
//                                      LastDate 
//                                  FROM SearchHistory";
//                using (var command = new SqlCommand(sql, conn))
//                {
//                    using (SqlDataReader reader = command.ExecuteReader())
//                    {
//                        return DataTableHelper.GetSearchHistoryEntities(reader);
//                    }
//                }
//            }
//            catch (SqlException ex)
//            {
//                throw new Exception("Error while get all products.", ex);
//            }

        }

        public static void UpdateSearchHistory(SearchHistoryEntity dtoSearchHistory)
        {
            var conn = DalUtilities.GetConnection();
            string sql = @"if(exists(select top 1 * from searchhistory where text=@Text))
                              update searchhistory set count = count + 1 
                           else
                              insert searchhistory(text, link, count) values(@Text, @Link, 1)";
            using (var command = new SqlCommand(sql, conn))
            {
                command.Parameters.AddWithValue("@Text", dtoSearchHistory.Text);
                command.Parameters.AddWithValue("@Link", dtoSearchHistory.Link);
                command.ExecuteNonQuery();
            }
        }

        public static int ExecuteNonQuery(SqlCommand sqlCmm)
        {
            var sqlCnn = DalUtilities.GetConnection();
            ValidateSqlCnn(sqlCnn);

            sqlCmm.Connection = sqlCnn;
            int effectedRows = sqlCmm.ExecuteNonQuery();

            CloseSqlCnn(sqlCnn);

            return effectedRows;
        }

        private static void ValidateSqlCnn(SqlConnection sqlCnn)
        {
            if (sqlCnn.State == System.Data.ConnectionState.Closed)
                sqlCnn.Open();
        }

        private static void CloseSqlCnn(SqlConnection sqlCnn)
        {
            sqlCnn.Close();
        }

        public static DataTable ExecuteQuery(string queryString)
        {
            var sqlCnn = DalUtilities.GetConnection();
            ValidateSqlCnn(sqlCnn);

            DataTable datas = new DataTable();
            using (SqlDataAdapter adapter = new SqlDataAdapter(queryString, sqlCnn))
            {
                adapter.Fill(datas);
            }

            CloseSqlCnn(sqlCnn);

            return datas;
        }
    }
}